from db.duckdb_helper import DuckDBHelper
from db.mssql import SQLServer
from datetime import datetime, timedelta
from typing import List, Union, Optional

def insert_admission_info(start_date, end_date):
    """
    生成入院病区、实际入院标识信息，数据源是zy_brry和zy_zkjl表。
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sql_str = f'''      
        with transfer as 
        (
        select 
        row_number() over(partition by zyh order by BQZXRQ,JLXH) as rn,
        zyh,
        hqks from zy_zkjl where BQZXRQ between '{start_date}' and now()  
        ),
        
        transfer_first as (
        select HQKS,ZYH from transfer where rn=1
        ),

        add_admission_ward as
        (
            select 
            brxm,
            zy_brry.zyh,zy_brry.ZYHM,date_trunc('day',zy_brry.RYRQ) as RYRQ,zy_brry.ryfs,
            COALESCE(transfer_first.hqks,zy_brry.BRKS) as hqks 
            from zy_brry left join transfer_first on zy_brry.ZYH = transfer_first.zyh
            where zy_brry.RYRQ between '{start_date}' and '{end_date}' and zy_brry.CYPB<>99
        ),

        --出院日期加1天
        discharge_date_add1 as 
        (
            select brxm,zyh,zyhm ,next_day as cyrq from
            (select 
            brxm,zyh,zyhm ,date_trunc('day',CYRQ)+1 as next_day,
            row_number() over (
                partition by brxm, date_trunc('day', CYRQ)  -- 分区依据是病人姓名和出院日期
                order by zyh  -- 或者选择其他唯一标识符如 JLXH 来确保排序稳定性
            ) as rn
            from zy_brry where CYRQ between  '{start_date}'  and  '{end_date}' 
            )
            where rn=1
        ),

        -- 前一天出院的拼接到入院病人表上
        add_again_admission as 
        (
        select aaw.zyh, aaw.ZYHM,aaw.brxm,aaw.ryfs,dda.zyhm as one_day_ago_admission,hqks
        from add_admission_ward aaw
        left join discharge_date_add1 dda on aaw.brxm=dda.brxm and aaw.ryrq=dda.cyrq
        ),
        -- is_new true:新入院 false：重整入院
        complate_select as 
        (
        select zyh as id,zyhm as admission_num,hqks  as admission_ward,
        case when one_day_ago_admission is null then true else false end as is_new,
        case when ryfs=1 then true else false end as is_emergency
        from add_again_admission
        )

        -- 插入admission_add_info表（本地表）
        insert into admission_add_info (id,admission_num,admission_ward,is_new,is_emergency) 
        select id,admission_num,admission_ward,is_new,is_emergency
        from complate_select
        ON CONFLICT (id) DO nothing
    '''

    dbh = DuckDBHelper()
    dbh.batch_insert_sql(sql_str)
    # print(dbh.query_df('select * from admission_add_info limit 10'))


def insert_discharge_info(start_date, end_date):
    """
    生成出院病区、实际出院标识信息，数据源是zy_brry表。
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sql_str = f'''
        with addmission_date_add_1 as
        (
        select zyh,cast(date_trunc('day',RYRQ)-1 as date)  as addmission_date,
        brxm
        from zy_brry where CYPB<>99 and RYRQ between '{start_date}' and '{end_date}'
        ),
        
        discharge_with_id as
        (  
        select zyh,ZYHM,date_trunc('day',CYRQ) as discharge_date,
        brxm
        from zy_brry
        where CYPB<>99 and CYRQ between '{start_date}' and '{end_date}'
        ),

        join_admission_date as
        (select dwi.zyh,dwi.zyhm,ada.brxm
        from discharge_with_id dwi
        left join addmission_date_add_1 ada on dwi.discharge_date=ada.addmission_date and dwi.brxm=ada.brxm
        ),
        -- is_discharge：true出院，false结算
        complet_select as
        (
        select zyh as id,zyhm as admission_num,
        case when brxm is null then true else false end as is_discharge
        from join_admission_date
        )

        -- insert
        insert into discharge_add_info(id,admission_num,is_discharge) 
        select id,admission_num,is_discharge
        from complet_select
        ON CONFLICT (id) DO nothing
        '''
    
    dbh = DuckDBHelper()
    dbh.batch_insert_sql(sql_str)


def insert_journal_info(start_date, end_date):
    """
    生成病人就诊信息，数据源是zy_brry表。
    :param start_date: 起始日期
    :param end_date: 结束日期
    :return: None
    """
    sql_str = f'''
        with admission_patient_count_ward_day as
        (  
        -- 病区每日入院人次汇总
        select date_trunc('day',ryrq) as series_day,ryks as ward,count(zyh) as admission_count
        from zy_brry
        where CYPB<>99 and ryrq between '{start_date}' and '{end_date}'
        group by date_trunc('day',RYRQ),RYKS
        order by series_day
        ),

        -- 病区每日新病人汇总
        new_admission_patient_count_ward_day as
        (
        select date_trunc('day',ryrq) as series_day,ryks as ward,count(zyh) as new_admission_count 
        from admission_add_info adi
        right join zy_brry on adi.id=zy_brry.ZYH
        where adi.is_new and ryrq between '{start_date}' and '{end_date}' and CYPB<>99
        group by date_trunc('day',RYRQ),RYKS
        order by series_day
        ),

        -- 病区每日急诊入院人次汇总
        emergency_admission_count as
        (
        select date_trunc('day',ryrq) as series_day,ryks as ward,count(zyh) as emergency_admission_count
        from zy_brry
        where CYPB<>99 and ryfs=1 and ryrq between '{start_date}' and '{end_date}'
        group by date_trunc('day',RYRQ),RYKS
        order by series_day
        ),

        --病区每日转入人次
        transferrd_in as
        (
        select date_trunc('day',BQZXRQ) as series_day,HHKS as ward,count(ZYH) as transfer_in_count
        from zy_zkjl
        where BQZXRQ between '{start_date}' and '{end_date}'
        group by date_trunc('day',BQZXRQ),HHKS
        order by series_day,HHKS
        ),

        --病区每日转出人次
        transferrd_out as
        (
        select date_trunc('day',BQZXRQ) as series_day,HQKS as ward,count(ZYH) as transfer_out_count
        from zy_zkjl
        where BQZXRQ between '{start_date}' and '{end_date}'
        group by date_trunc('day',BQZXRQ),HQKS
        order by series_day,ward
        ),

        -- 病区每日出院病人次
        discharge_patient_count as 
        (
        select date_trunc('day',CYRQ) as series_day,BRKS as ward,count(zyh) as discharge_count
        from zy_brry
        where CYPB<>99 and CYRQ between '{start_date}' and '{end_date}'
        group by date_trunc('day',CYRQ),BRKS
        order by series_day,ward
        ),

        -- 病区每日实际出院病人
        true_discharge_patient_count as 
        (
        select date_trunc('day',CYRQ) as series_day,BRKS as ward,count(zyh) as true_discharge_count
        from zy_brry zb
        left join discharge_add_info  dai on zb.zyh=dai.id 
        where zb.CYPB<>99 and CYRQ between '{start_date}' and '{end_date}' and dai.is_discharge
        group by series_day,ward
        order by series_day,ward
        ),

        -- 死亡病例
        deaths_count as 
        (
        select date_trunc('day',CYRQ) as series_day,BRKS as ward,count(zyh) as deaths_count
        from zy_brry
        where cypb<>99 and CYFS=4 and CYRQ between '{start_date}' and '{end_date}'
        group by series_day,ward
        order by series_day,ward
        ),        

        ---------下面是病区每日在院人数和实际占用床日数
        -----生成日期序列
        series_date as 
        (
        select series_date.generate_series::date as series_day
        from
        generate_series(date '{start_date}',date '{end_date.split()[0]}',interval 1 day) as series_date
        ),

        ----获取病区列表
        series_ward as 
        (
            select distinct ward from
            (
            select distinct admission_ward as ward 
            from admission_add_info adi left join zy_brry on adi.id=zy_brry.zyh 
            where RYRQ between '{start_date}' and '{end_date}' and CYPB<>99
            
            union all
            select distinct BRKS as ward from zy_brry
            where CYRQ between '{start_date}' and '{end_date}' and CYPB<>99
            
            union all
            select distinct hqks as ward
            from zy_zkjl
            where BQZXRQ between '{start_date}' and '{end_date}'
            
            union all
            select distinct HHKS as ward
            from zy_zkjl
            where BQZXRQ between '{start_date}' and '{end_date}'
            )
        ),

        -- 每个病人 按曾经住过的病区分段,转科前，转科后
        -- 基础数据：每天每个病人的位置状态
        patient_ward_daily AS (
            -- 转科前的位置状态，或不曾转科的入出院日期
        select zyh,start_date,end_date,ward from(
            select zyh,start_date,end_date,ward,
            row_number() over(partition by zyh,start_date,end_date order by zyh,start_date desc) as rn 
            from
            (
            SELECT 
                zyh,
                ryrq::date as start_date,
                -- 结束日期.如果有转科记录,取最早的转科日期,否取即是没有转科记录,取出院日期,未出院取查询期间的结束日期
                coalesce(
                    (select min(BQZXRQ) from zy_zkjl
                    where zy_zkjl.ZYH=admission.ZYH),
                    coalesce(cyrq,date '{end_date.split()[0]}')
                ):: date as end_date,
                admission_ward as ward
                FROM --用个子查询来拼接入院病区字段
                (select ZYH,RYRQ,CYRQ,admission_ward
                    from zy_brry left join admission_add_info on zy_brry.zyh =admission_add_info.id
                    where CYPB<>99 and RYRQ<='{end_date}' and (CYRQ>='{start_date}' or CYRQ is null)
                ) admission
            
            UNION ALL
        
            -- 转科后的位置状态
            select 
                zy_zkjl.zyh,
                zy_zkjl.bqzxrq ::date as start_date,
                -- 后一次转科时间作为当前转科记录的截止时间，出院时间作为最后一次转科的结束时间
                coalesce(
                lead(zy_zkjl.BQZXRQ,1) over(partition by zy_zkjl.zyh order by JLXH ) ,
                coalesce(zy_brry.CYRQ,date '{end_date.split()[0]}')  -- 没出院就以查询期截止时间作为截止时间
                )::date as end_date,
                zy_zkjl.HHKS as ward
            from (select jlxh, zyh,bqzxrq,hhks from zy_zkjl 
            where zyh in (select zyh from zy_brry where ryrq between '{start_date}' and '{end_date}') 
            and zy_zkjl.bqzxrq is not null) zy_zkjl
            left join zy_brry using(zyh)                
            )
            ) where rn=1  
        ),

        -- 日历日期和病区的笛卡尔积
        date_wards as  
        (
            select * from series_date cross join series_ward
        )  ,

        -- 每日每病区期末人数
        daily_ward_patient as (
            select dw.series_day,dw.ward ,count(zyh) as end_persons    
            from date_wards dw
            left join patient_ward_daily pwd on dw.series_day>=pwd.start_date and dw.series_day<pwd.end_date and dw.ward=pwd.ward     
            group by dw.series_day,dw.ward    
        ),

        -- 每日每病区占用床日数
        daily_ward_bed_days as
        (
            select series_day,ward,
            case when zyh is null then end_persons else end_persons+1 end as bed_days
            from 
            (
            select dwp.*,one_day.zyh
            from daily_ward_patient dwp 
            left join (select zyh, RYRQ,BRKS from zy_brry where RYRQ=CYRQ and CYPB<>99) one_day  on dwp.series_day=one_day.ryrq and dwp.ward=one_day.brks
            )
        ),

        -- 每日每病区出院患者占用床日数
        daily_ward_discharge_bed_days as 
        (
            select date_trunc('day',series_day) as series_day,ward,round(sum(bed_days),2) as discharge_bed_days
            from (
            select date_trunc('day',CYRQ) as series_day,BRKS as ward,greatest(date_diff('day',RYRQ,CYRQ),1) as bed_days 
            from zy_brry 
            where CYPB<>99 and CYRQ between '{start_date}' and '{end_date}'
            )
            group by date_trunc('day',series_day),ward  
        ) ,
        
        -- 拼接全部指标
        select_journal as 
        (
            select 
            series_day,ward ,
            (coalesce(end_persons,0)+coalesce(discharge_count,0)+coalesce(transfer_out_count,0)-coalesce(transfer_in_count,0)-coalesce(admission_count,0)) as start_persons,
            admission_count as admission_persons ,new_admission_count as new_admission_persons,
            emergency_admission_count as emergency_admission_persons ,transfer_in_count as transfer_in_persons,transfer_out_count as transfer_out_persons ,discharge_count as discharge_persons,
            true_discharge_count as true_discharge_persons ,deaths_count as deaths_persons,end_persons ,bed_days ,discharge_bed_days 
            from date_wards
            left join admission_patient_count_ward_day using(series_day,ward)
            left join new_admission_patient_count_ward_day using(series_day,ward)
            left join emergency_admission_count using(series_day,ward)
            left join transferrd_in using(series_day,ward)
            left join transferrd_out using(series_day,ward)
            left join discharge_patient_count using(series_day,ward)
            left join true_discharge_patient_count using(series_day,ward)
            left join deaths_count using(series_day,ward)
            left join daily_ward_patient using(series_day,ward)
            left join daily_ward_bed_days using(series_day,ward)
            left join daily_ward_discharge_bed_days using(series_day,ward)
            order by series_day,ward
        )

        -- 写入表
        INSERT INTO journal (
            series_day, ward, start_persons, admission_persons, new_admission_persons,
            emergency_admission_persons, transfer_in_persons, transfer_out_persons,
            discharge_persons, true_discharge_persons, deaths_persons, end_persons,
            bed_days, discharge_bed_days
        )
        SELECT 
            series_day, ward, start_persons, admission_persons, new_admission_persons,
            emergency_admission_persons, transfer_in_persons, transfer_out_persons,
            discharge_persons, true_discharge_persons, deaths_persons, end_persons,
            bed_days, discharge_bed_days
        FROM select_journal
        ON CONFLICT (series_day, ward) DO UPDATE SET
            start_persons = EXCLUDED.start_persons,
            admission_persons = EXCLUDED.admission_persons,  -- 使用 EXCLUDED.<insert_column_name>
            new_admission_persons = EXCLUDED.new_admission_persons,
            emergency_admission_persons = EXCLUDED.emergency_admission_persons,
            transfer_in_persons = EXCLUDED.transfer_in_persons,
            transfer_out_persons = EXCLUDED.transfer_out_persons,
            discharge_persons = EXCLUDED.discharge_persons,
            true_discharge_persons = EXCLUDED.true_discharge_persons,
            deaths_persons = EXCLUDED.deaths_persons,
            end_persons = EXCLUDED.end_persons,
            bed_days = EXCLUDED.bed_days,
            discharge_bed_days = EXCLUDED.discharge_bed_days
    '''
    
    dbh = DuckDBHelper()
    dbh.batch_insert_sql(sql_str)


def query_journal_info(start_date:str,end_date:str)->Union[pd.DataFrame]:
    """
    查询指定日期范围内的病区就诊信息
    :param start_date: 起始日期，合法的日期字符串，只需提供日期部分，如'2025-07-01'
    :param end_date: 结束日期,必须精确到秒，如'2025-07-31 23:59:59'
    :return: DataFrame
    """
    sql_str = f'''
        -- 病区每日在院人数和实际占用床日数
        WITH series_date AS 
        (
            -- 生成日期序列
            SELECT 
                DATEADD(DAY, n.number, CAST(':start_date' AS DATE)) AS series_day
            FROM 
                master..spt_values n
            WHERE 
                n.type = 'P' 
                AND n.number <= DATEDIFF(DAY, CAST(':start_date' AS DATE), CAST(':end_date' AS DATE))
        ),

        -- 获取病区列表
        series_ward AS 
        (
            SELECT DISTINCT ward 
            FROM
            (        
                SELECT DISTINCT ksdm AS ward 
                FROM ys_zy_jzjl
                WHERE ryrq >= ':start_date'            
                
                UNION ALL
                
                SELECT DISTINCT hqks AS ward
                FROM zy_zkjl
                WHERE BQZXRQ >= ':start_date'
                
                UNION ALL
                
                SELECT DISTINCT HHKS AS ward
                FROM zy_zkjl
                WHERE BQZXRQ >= ':start_date' 
            ) t
        ),
        -- 日期列表和病区列表的笛卡尔积
        date_ward as 
        (
        SELECT series_day,ward 
        from series_date cross join series_ward
        ),

        -- 有效住院记录，ys_zy_jzjl中的有效住院记录
        effectively_admission as (
        SELECT jzjl.JZHM,jzjl.KSDM,jzjl.RYRQ,jzjl.CYRQ,brry.cyfs
        from YS_ZY_JZJL jzjl,ZY_BRRY brry
        where jzjl.jzhm=brry.zyh and (jzjl.CYRQ>= ':start_date' or jzjl.CYRQ is null) and  brry.CYPB<>99
        ),

        -- 为转科记录创建下一个转科时间的CTE
        next_transfer AS (
            SELECT 
                z1.jlxh,
                z1.zyh,
                z1.bqzxrq,
                z1.HQKS,
                z1.hhks,
                (SELECT MIN(z2.bqzxrq)   --没有处理最后一条转科记录的结束时间
                FROM zy_zkjl z2 
                WHERE z2.zyh = z1.zyh 
                    AND z2.bqzxrq > z1.bqzxrq
                    AND z2.bqzxrq IS NOT NULL) AS next_transfer_date
            FROM zy_zkjl z1
            WHERE z1.bqzxrq IS NOT NULL and zyh in (select jzhm from effectively_admission)
        ),

        -- 每个病人按曾经住过的病区分段（转科前，转科后）
        patient_ward_daily AS (
            SELECT JZHM, start_date, end_date, ward
            FROM (
                SELECT JZHM, start_date, end_date, ward,
                    ROW_NUMBER() OVER(PARTITION BY JZHM, start_date, end_date ORDER BY start_date DESC) AS rn 
                FROM
                (
                    -- 转科前的位置状态，或不曾转科的入出院日期
                    SELECT 
                        JZHM,
                        ryrq AS start_date,
                        COALESCE(
                            (SELECT MIN(BQZXRQ) FROM zy_zkjl WHERE zy_zkjl.ZYH = admission.JZHM),
                            COALESCE(cyrq, GETDATE() )                     -- 期间结束时间 此处可以用getdate()
                        ) AS end_date,
                        admission_ward AS ward
                    FROM 
                    (
                        SELECT jzjl.JZHM, jzjl.RYRQ, jzjl.CYRQ,      --子查询，用于获取有正确入院病区信息的病人信息
                        COALESCE(                                        
                        (SELECT top 1 HQKS from zy_zkjl zkjl where jzjl.jzhm=zkjl.ZYH ORDER BY BQZXRQ),
                        KSDM
                        ) as admission_ward
                        FROM effectively_admission jzjl            
                                            
                    ) admission
                    
                    UNION ALL
                
                    -- 转科后的位置状态
                    SELECT 
                        nt.zyh as JZHM,
                        nt.bqzxrq  AS start_date,
                        COALESCE(
                            nt.next_transfer_date,
                            COALESCE(ea.CYRQ, GETDATE())   -- 未出院，出院时间用查询截止时间或者getdate()
                        ) AS end_date,
                        nt.hhks AS ward
                    FROM next_transfer nt
                    LEFT JOIN effectively_admission ea ON nt.zyh = ea.JZHM
                    
                ) t1
            ) t2
            WHERE rn = 1    -- 去重，同一病人开始日期，结束日期都相同的记录去重
        ),

        -- 每日每病区期末人数
        daily_ward_patient AS (
            SELECT 
                dw.series_day,
                dw.ward,
                --JZHM,Start_Date,end_date
                count(JZHM) as end_persons    
            FROM date_ward dw
            left join patient_ward_daily pwd 
            ON dw.series_day >= cast(pwd.start_date  as date)   -- 容易出错。start_date 是datetime，有具体时间，转化为date
            AND dw.series_day < cast(pwd.end_date  as date)   -- 容易出错。end_date 是datetime，有具体时间，转化为date
            AND dw.ward = pwd.ward     
            GROUP BY dw.series_day, dw.ward    
        ),
        -- 每日每病区占用床日数
        daily_ward_bed_days AS
        (
            SELECT 
            dwp.series_day,
            dwp.ward,
            CASE 
                WHEN one_day.JZHM IS NULL THEN dwp.end_persons 
                ELSE dwp.end_persons + 1 
            END AS bed_days
        FROM 
            daily_ward_patient dwp 
            LEFT JOIN (
                SELECT 
                    JZHM, 
                    CAST(RYRQ AS DATE) AS ry_date,
                    KSDM
                FROM effectively_admission 
                WHERE CAST(RYRQ AS DATE) = CAST(CYRQ AS DATE)  -- 当天入院当天出院
            ) one_day 
                ON dwp.series_day = one_day.ry_date
                AND dwp.ward = one_day.KSDM
        ),

        --每日每病区出院患者占用床日数
        daily_ward_discharge_bed_days AS 
        (
            SELECT 
                CAST(series_day AS DATE) AS series_day,
                ward,
                SUM(bed_days) AS discharge_bed_days
            FROM (
                SELECT 
                    CAST(CYRQ AS DATE) AS series_day,
                    KSDM AS ward,
                    CASE 
                        WHEN DATEDIFF(DAY, RYRQ, CYRQ) <= 0 THEN 1 
                        ELSE DATEDIFF(DAY, RYRQ, CYRQ) 
                    END AS bed_days 
                FROM effectively_admission 
                WHERE   CYRQ between ':start_date' and ':end_date'
            ) t
            GROUP BY CAST(series_day AS DATE), ward  
        ),

        -- 有效转科记录
        effectively_transfer as 
        (
        SELECT zyh,HQKS,HHKS,BQZXRQ
        from ZY_ZKJL
        where BQZXRQ is not NULL
            and zyh in (select JZHM FROM effectively_admission)
        ),

        -- 转出人次
        transfer_out as 
        (
        select cast(BQZXRQ as date) as series_day, HQKS as ward,COUNT(ZYH) as transfer_out_persons
        from effectively_transfer
        GROUP BY cast(BQZXRQ as date),HQKS
        ),

        -- 转入人次
        transfer_in AS
        (
        select cast(BQZXRQ as date) as series_day, HHKS as ward,COUNT(ZYH) as transfer_in_persons
        from effectively_transfer
        GROUP BY cast(BQZXRQ as date),HHKS
        ),

        -- 死亡病例
        death_persons as 
        (
        SELECT CAST(CYRQ as date) as series_day,ksdm as ward,COUNT(JZHM) as deaths_persons
        from effectively_admission
        WHERE CYFS=4  
        GROUP BY CAST(cyrq as date),ksdm
        )

        select  dw.series_day,dw.ward,ks.ksmc,ti.transfer_in_persons,tt.transfer_out_persons,
            dp.deaths_persons,dwp.end_persons,bed_days,discharge_bed_days
        from date_ward dw 
        left join transfer_in ti 
            on dw.series_day=ti.series_day and dw.ward=ti.ward
        left join transfer_out tt 
            on dw.series_day=tt.series_day and dw.ward=tt.ward
        left join death_persons dp
            on dw.series_day=dp.series_day and dw.ward=dp.ward
        left join daily_ward_patient dwp
            on dw.series_day=dwp.series_day and dw.ward=dwp.ward
        left join daily_ward_bed_days dwpd 
            on dw.series_day=dwpd.series_day and dw.ward=dwpd.ward
        left join daily_ward_discharge_bed_days dwdb 
            on dw.series_day=dwdb.series_day and dw.ward=dwdb.ward
        left join GY_KSDM ks on dw.WARD=ks.KSDM
        where dw.series_day BETWEEN ':start_date' and ':end_date' ;
    '''
    mssql_db = SQLServer()
    try:
        mssql_db.query_df(sql_str,params={'start_date':start_date,'end_date':end_date})
    except Exception as e:
        print('获取数据失败,请查看日志。')
        return None
